package net.oschina.db;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Created by rex on 2015/3/10.
 */
public class SQL {
    Connection con = null;
    ResultSetHandler<Object[]> h = new ResultSetHandler<Object[]>() {
        public Object[] handle(ResultSet rs) throws SQLException {
            if (!rs.next()) {
                return null;
            }

            ResultSetMetaData meta = rs.getMetaData();
            int cols = meta.getColumnCount();
            Object[] result = new Object[cols];

            for (int i = 0; i < cols; i++) {
                result[i] = rs.getObject(i + 1);
            }

            return result;
        }
    };

    public SQL() throws SQLException {
        try {
            this.con = DBPool.getInstance().getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Add user into database
     * @param username
     * @return
     */
    public boolean AddUser(String username){
        QueryRunner run = new QueryRunner();
        Object[] result = null;

        try {
            result = run.insert(this.con, "INSERT INTO user(username) VALUES(?)", h, username);
        } catch (SQLException e) {
            System.out.println("[W] user maybe already exist, insert failed.");
            return false;
        }

        return true;
    }

    /**
     * update email into database
     * @param username
     * @param email
     * @param raw
     * @return
     */
    public boolean UpdateEmail(String username, String email, String raw){
        QueryRunner run = new QueryRunner();
        int result = 0;

        try {
            result = run.update(this.con, "UPDATE user SET email = ? WHERE username = ?", email, username);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

        if (result <= 0){
            return false;
        }

        return true;
    }

    /**
     * find userinfo by username from database
     * @param username
     * @return
     */
    public boolean FindUser(String username){
        QueryRunner run = new QueryRunner();
        Object[] result = null;

        try {
            result = run.query(this.con, "SELECT * FROM user WHERE username=?", h, username);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

//        System.out.println("[result]");
//        this.DumpObject(result);

        return true;
    }

    /**
     * get user list form database
     */
    public List<Map<String, Object>> GetUserList(){
        QueryRunner run = new QueryRunner();
        List<Map<String, Object>> list = null;

        try {
            list = run.query(this.con, "select username from user where email is NULL or email=''", new MapListHandler(), (Object[]) null);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    /**
     * patch get user list from database
     * @return
     */
    public List<Map<String, Object>> GetUserListPatch(){
        QueryRunner run = new QueryRunner();
        List<Map<String, Object>> list = null;

        try {
            list = run.query(this.con, "select username from user where email='NOT EXIST'", new MapListHandler(), (Object[]) null);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    public List<Map<String, Object>> GetUserEmailList(){
        QueryRunner run = new QueryRunner();
        List<Map<String, Object>> list = null;

        try {
            list = run.query(this.con, "select username,email from user where email!='NOT EXIST' and send='N'", new MapListHandler(), (Object[]) null);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    public boolean UpdateEmailHasSend(String username){
        QueryRunner run = new QueryRunner();
        int result = 0;

        try {
            result = run.update(this.con, "UPDATE user SET send = ? WHERE username = ?", "Y", username);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

        if (result <= 0){
            return false;
        }

        return true;
    }

    /**
     * dump object
     * @param myobj
     */
    public static void DumpObject(Object[] myobj){
        String str;
        for(int i = 0; i < myobj.length; i++){
            Object obj  = myobj[i];
            str  = obj.toString();
            System.out.println(str);
        }
    }

}
